package com.community.sqlapp.service;

import com.alibaba.fastjson.JSONObject;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * mysql查询工具实现类
 */
@Service
@RequiredArgsConstructor
public class DatabaseService {

    private final JdbcTemplate jdbcTemplate;

    /**
     * 获取表结构信息
     */
    public List<Map<String, Object>> getTableStructure(String tableName) {
        String sql = "DESCRIBE " + tableName;
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 执行SQL查询并返回结果
     */
    public Map<String, Object> executeQuery(String sql) {
        Map<String, Object> result = new HashMap<>();
        if (sql == null || sql.trim().isEmpty()) {
            result.put("success", false);
            result.put("error", "SQL语句不能为空");
            return result;
        }
        try {
            if (sql.trim().toLowerCase().startsWith("select") ||
                    sql.trim().toLowerCase().startsWith("show") ||
                    sql.trim().toLowerCase().startsWith("describe")) {

                List<Map<String, Object>> data = new ArrayList<>();
                List<String> columns = new ArrayList<>();

                jdbcTemplate.query(sql, rs -> {
                    java.sql.ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    // 统计字段名出现次数
                    Map<String, Integer> nameCount = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String colName = metaData.getColumnLabel(i);
                        nameCount.put(colName, nameCount.getOrDefault(colName, 0) + 1);
                    }
                    // 构建最终字段名（有重复的加表名前缀）
                    List<String> finalColNames = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String colName = metaData.getColumnLabel(i);
                        if (nameCount.get(colName) > 1) {
                            String tableName = metaData.getTableName(i);
                            if (tableName == null || tableName.isEmpty()) {
                                tableName = "t" + i;
                            }
                            finalColNames.add(tableName + "_" + colName);
                        } else {
                            finalColNames.add(colName);
                        }
                    }
                    columns.addAll(finalColNames);

                    // 处理每一行数据
                    do {
                        Map<String, Object> row = new LinkedHashMap<>();
                        for (int i = 1; i <= columnCount; i++) {
                            String colName = finalColNames.get(i - 1);
                            row.put(colName, rs.getObject(i));
                        }
                        data.add(row);
                    } while (rs.next());
                });

                result.put("success", true);
                result.put("data", data);
                result.put("columns", columns);
                result.put("rowCount", data.size());
            } else {
                int rowsAffected = jdbcTemplate.update(sql);
                result.put("success", true);
                result.put("rowsAffected", rowsAffected);
                result.put("message", "执行成功，影响了 " + rowsAffected + " 行数据");
            }
        } catch (Exception e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        return result;
    }

    public boolean isConnectionValid() {
        try {
            // 检查数据库连接是否有效
            String testSql = "SELECT 1";
            jdbcTemplate.queryForList(testSql);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}